/*
 * Author :zhaoningbo
 * Date   :2011-8-10 16:39:54
 * Email  :zhaoningbo@hotmail.com
 * Blog   :http://zhaoningbo.iteye.com/
 */
package com.number.app.excel;

import java.io.File;
import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import com.number.util.db.DBManager;
import com.number.util.template.ExcelTemplateManager;

/**
 * 读取Excel文件内容
 * 
 * @author zhnb
 * 
 */
public class ExcelManager implements Serializable {

    private static final long serialVersionUID = 807307010492303233L;

    // 操作状态
    private final String STATUS_FAILD = "N/A";
    private final String STATUS_SUCCESS = "OK!";

    /**
     * 将Excel中的记录导入数据库表中
     * 
     * @param fileName
     *            文件名
     * @return 执行状态
     */
    public String importExcelIntoTable(String fileName) {

        Workbook wb = null;
        try {
            // 打开Excel
            wb = Workbook.getWorkbook(new File(fileName));

            // 获取测试Sheet页
            Sheet testSheet = wb.getSheet("Sheet_test");

            // 获取行、列数目
            int rowCount = testSheet.getRows();
            int colCount = testSheet.getColumns();

            // sheet非空
            if (rowCount * colCount > 0) {

                // 插入数据库
                Connection conn = DBManager.getConnection();
                PreparedStatement pstm = null;

                try {
                    // 批量提交SQL语句(100K数量级条记录时会出问题)
                    pstm = conn
                            .prepareStatement("insert into ccxe.xls_test(xt_sn, xt_enable, "
                                    + "xt_description, xt_value) values(?,?,?,?)");
                    // 循环行
                    for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
                        pstm.setString(1, testSheet.getCell(0, rowIndex)
                                .getContents());
                        pstm.setDouble(2, new Double(testSheet.getCell(1,
                                rowIndex).getContents()));
                        pstm.setString(3, testSheet.getCell(2, rowIndex)
                                .getContents());
                        pstm.setString(4, testSheet.getCell(3, rowIndex)
                                .getContents());
                        pstm.addBatch();
                    }

                    int[] countArr = pstm.executeBatch();
                    System.out.println(countArr == null ? null
                            : countArr.length);

                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    // 关闭连接
                    try {
                        pstm.close();
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch (BiffException e) {
            e.printStackTrace();
            return STATUS_FAILD;
        } catch (IOException e) {
            e.printStackTrace();
            return STATUS_FAILD;
        } finally {
            // 关闭
            wb.close();
        }

        return STATUS_SUCCESS;
    }

    /**
     * 指定模板导入Excel数据
     * 
     * @param excelFile
     *            Excel文件（包含路径）
     * @param sheetName
     *            待导数据所在Sheet页名称
     * @param templateFileName
     *            模板文件名
     * @return
     */
    public String importExcelIntoTableByTemplate(String excelFile,
            String sheetName, String templateFileName) {

        // 错误追踪
        String status = "";
        
        // 获取模板信息
        ExcelTemplateManager excelTemplateManager = new ExcelTemplateManager();
        List<String[]> templateList = excelTemplateManager
                .getTemplate(templateFileName);

        // 读取Excel文件指定Sheet页的数据并按模板导入库中
        Workbook wb = null;
        try {
            wb = Workbook.getWorkbook(new File(excelFile));
            // 待导数据Sheet页
            Sheet currSheet = wb.getSheet(sheetName);
            // 行、列信息
            int rowCount = currSheet.getRows();
            int colCount = currSheet.getColumns();

            // 表非空
            if (rowCount * colCount > 0) {
                // 导入数据库
                Connection conn = DBManager.getConnection();
                PreparedStatement pstm = null;
                try {
                    // 从模板获取SQL模型
                    String[] sqlArr = templateList.get(0);
                    // SQL模型存在
                    if ((sqlArr != null) && (sqlArr.length > 1)
                            && (sqlArr[1].trim().length() > 0)) {
                        pstm = conn.prepareStatement(sqlArr[1].trim());

                        // 向PreparedStatement添加待导入数据(.addBatch()方式)
                        // 外层循环Excel行
                        for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
                            // 外层循环模板列
                            for (int currIndex = 1; currIndex < (templateList
                                    .size()); currIndex++) {
                                String[] currTemplate = templateList
                                        .get(currIndex);
                                // 必备字段齐全（行号,类型）
                                if (currTemplate != null
                                        && currTemplate.length > 1) {
                                    // 设置值
                                    // 模板长度小于3，则验证属性为NULL。
                                    String validator = null;
                                    if (currTemplate.length > 2) {
                                        validator = currTemplate[2];
                                    }
                                    status += this.setIntoPreparedStatement(pstm,
                                            currIndex, currTemplate[1],
                                            currSheet.getCell(currIndex - 1,
                                                    rowIndex).getContents(),
                                            validator);
                                }
                            }
                            // 添加为批量任务
                            pstm.addBatch();
                        }
                        // 设置参数校验通过
                        if (status.length()<1) {
                            // 执行批量任务(对Oracle此返回值无意义恒为-2)
                            pstm.executeBatch();
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    // 释放连接
                    try {
                        if (pstm != null) {
                            pstm.close();
                        }
                        if (pstm != null) {
                            conn.close();
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭工作簿
            wb.close();
        }

        // 错误追踪无错
        if (status.length()<1) {
            return STATUS_SUCCESS;
        }
        // 错误追踪有错
        else {
            System.err.println(status);
            return STATUS_FAILD;
        }
    }

    /**
     * 设置PreparedStatement参数
     * @param pstm              PreparedStatement
     * @param colIndex          第几个参数
     * @param type              值类型
     * @param value             值
     * @param validator         校验
     * @return
     */
    public String setIntoPreparedStatement(PreparedStatement pstm, int colIndex,
            String type, String value, String validator) {
        
        StringBuffer setterTrace = new StringBuffer();
        try {
            // 字符串
            if ("string".equalsIgnoreCase(type)) {
                if (value != null && value.length() < new Integer(validator)) {
                    pstm.setString(colIndex, value);
                } else {
                    setterTrace.append("col_" + colIndex + ": " + type + "("
                            + validator + "), " + value + " is illegal.\n");
                }
            }
            // 浮点
            else if ("double".equalsIgnoreCase(type)) {
                pstm.setDouble(colIndex, new Double(value));
            } else {
                setterTrace.append("col_" + colIndex + ": " + type + "("
                        + validator + "), " + value + " is illegal.\n");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return setterTrace.toString();
    }
}
